package com.orangelife.service.outexcell;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;

public class CreateExcellByPoi {

	/**
	 * 功能：设置单元格风格的属性
	 * */
	public static HSSFCellStyle SetCellStyleAttribute(int stylenum,
			HSSFCellStyle style, HSSFFont font) {

		switch (stylenum) {

		// 单元格格式一:水平居中，垂直居中
		case 1:
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFont(font);

			break;
		// 单元格格式二:水平居中，垂直居中，有边框
		case 2:
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style.setFont(font);

			break;
		// 单元格格式三:垂直居中
		case 3:
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setFont(font);

			break;

		default:
			break;
		}

		return style;

	}

	/**
	 * 功能：设置2007以及以上版本单元格风格的属性
	 * */
	public static CellStyle SetXCellStyleAttribute(int stylenum,
			CellStyle style, Font font) {

		switch (stylenum) {

		// 单元格格式一:水平居中，垂直居中
		case 1:
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFont(font);

			break;
		// 单元格格式二:水平居中，垂直居中，有边框
		case 2:
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			style.setFont(font);

			break;
		// 单元格格式三:垂直居中
		case 3:
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style.setFont(font);

			break;

		default:
			break;
		}

		return style;

	}

	/**
	 * 设置字体风格的属性
	 * */
	public static void setFontStyleAttribute(int fontnum, HSSFFont font) {

		font.setColor(HSSFColor.BLACK.index);

		switch (fontnum) {
		case 1:// 字体风格：微软雅黑，12号字体，黑色，加粗
			font.setFontName("微软雅黑");
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			break;

		case 2:// 字体风格：微软雅黑，10号字体，黑色
			font.setFontName("微软雅黑");
			font.setFontHeightInPoints((short) 10);

			break;

		case 3:// 字体风格：微软雅黑，9号字体，黑色
			font.setFontName("微软雅黑");
			font.setFontHeightInPoints((short) 9);

			break;

		case 4:// 字体风格：宋体，12号字体，黑色，加粗
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			break;

		case 5:// 字体风格：宋体，10号字体，黑色
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 10);
			break;

		case 6:// 字体风格：宋体，9号字体，黑色
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 9);
			break;

		default:
			break;
		}

	}

	/**
	 * 设置2007以及以上版本字体风格的属性
	 * */
	public static void setXFontStyleAttribute(int fontnum, Font font) {

		font.setColor(HSSFColor.BLACK.index);

		switch (fontnum) {
		case 1:// 字体风格：微软雅黑，12号字体，黑色，加粗
			font.setFontName("微软雅黑");
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			break;

		case 2:// 字体风格：微软雅黑，10号字体，黑色
			font.setFontName("微软雅黑");
			font.setFontHeightInPoints((short) 10);

			break;

		case 3:// 字体风格：微软雅黑，9号字体，黑色
			font.setFontName("微软雅黑");
			font.setFontHeightInPoints((short) 9);

			break;

		case 4:// 字体风格：宋体，12号字体，黑色，加粗
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			break;

		case 5:// 字体风格：宋体，10号字体，黑色
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 10);
			break;

		case 6:// 字体风格：宋体，9号字体，黑色
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 9);
			break;

		default:
			break;
		}

	}

	public static int getPOIWidthByExcelPiex(int Piex) {

		return (int) (Piex * 37.04);

	}

	/**
	 * 功能：生成缴费明细表
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * 
	 * 
	 * 
	 * */
	public static void outScheduleOfFees(List<String> titles,
			List<List<String>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("缴费明细表.xls");

		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 26.25);
		Cell cell = rowBigtitle.createCell(0);

		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(1, font1);
		SetCellStyleAttribute(1, style1, font1);
		cell.setCellStyle(style1);
		cell.setCellValue("缴费明细表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		Row Subtitle = sheet.createRow(1);

		Subtitle.setHeightInPoints((float) 16.50);

		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(2, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName
				+ "                           统计时间：" + StatisticsTime
				+ "                          统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 21.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();
		setFontStyleAttribute(3, font3);

		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(33));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(115));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(80));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 18.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

				cellvalue.setCellValue(celltemp);

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 18.00);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("缴费合计（元）");
		Cell cell2total2 = rowtotal.createCell(1);
		cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));

		for (int k = 0; k < totals.size(); k++) {

			Cell cellvalue = rowtotal.createCell(k + 2);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 14.25);
		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0, 1));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutScheduleOfFees() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("缴费月份");
		titles.add("物业管理费");
		titles.add("公共维修金");
		titles.add("车位管理费");
		titles.add("车位维修金");
		titles.add("公摊水电费");
		titles.add("合计（元");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 20; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 8; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 6; k++) {

			if (k == 5) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outScheduleOfFees(titles, values, totals, "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "D:/缴费明细表.xls", null, 8);

	}

	/**
	 * 功能：生成缴费明细表(月度明细)
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * 
	 * 
	 * 
	 * */
	public static void outScheduleOfFeesByMonth(List<String> titles,
			List<List<String>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("缴费明细表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 26.25);
		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(1, font1);
		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("缴费明细表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 16.50);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(2, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName
				+ "                                 统计时间：" + StatisticsTime
				+ "                                  统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 21.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(3, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(33));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(115));
				break;
			case 5:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(155));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(81));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 18.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

				cellvalue.setCellValue(celltemp);

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 18.00);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
				totalrepeatsize - 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("缴费合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 14.25);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
				totalrepeatsize - 1));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutScheduleOfFeesByMonth() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("缴费时间");
		titles.add("是否开票");
		titles.add("发票单号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("支付方式");
		titles.add("物业管理费");
		titles.add("公共维修金");
		titles.add("车位管理费");
		titles.add("车位维修金");
		titles.add("公摊水电费");
		titles.add("合计（元");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 14; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 6; k++) {

			if (k == 5) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outScheduleOfFeesByMonth(titles, values, totals, "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "缴费明细表(月度明细).xls", null, 8, 14);

	}

	/**
	 * 功能：开单明细表
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * 
	 * 
	 * 
	 * */
	public static void outBillingSchedule(List<String> titles,
			List<List<String>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("开单明细表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 26.25);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("开单明细表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 16.50);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName
				+ "                                 统计时间：" + StatisticsTime
				+ "                                  统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 21.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(33));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(115));
				break;
			case 4:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(155));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(80));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 18.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

				cellvalue.setCellValue(celltemp);

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 18.00);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
				totalrepeatsize - 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("缴费合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 14.25);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
				totalrepeatsize - 1));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutBillingSchedule() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("开单时间");
		titles.add("发票单号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("物业管理费");
		titles.add("公共维修金");
		titles.add("车位管理费");
		titles.add("车位维修金");
		titles.add("公摊水电费");
		titles.add("合计（元");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 12; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 6; k++) {

			if (k == 5) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outBillingSchedule(titles, values, totals, "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "开单明细表.xls", null, 6, 12);

	}

	/**
	 * 功能：欠费统计表（单小区明细-按筛选条件导出）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * 
	 * */
	public static void outTuitionTablesBySingleCell(List<String> titles,
			List<List<String>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("欠费统计表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("欠费统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 20.25);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName + "              统计时间："
				+ StatisticsTime + "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 18.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(58));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(70));
				break;
			case 2:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(155));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(95));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 15.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));
				cellvalue.setCellValue(celltemp);

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 22.5);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
				totalrepeatsize - 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("欠费合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 21.00);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
				totalrepeatsize - 1));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutTuitionTablesBySingleCell() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("计费面积");
		titles.add("物业管理费");
		titles.add("公共维修金");
		titles.add("合计（元");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 8; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 3; k++) {

			if (k == 2) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outTuitionTablesBySingleCell(titles, values, totals, "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "欠费统计表(单小区明细-按筛选条件导出).xls",
				null, 5, 8);

	}

	/**
	 * 功能：欠费统计表（单小区明细-合并月份总计）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * 
	 * */
	public static void outTuitionTablesBySingleCellAndMergeMonth(
			List<String> titles, List<List<String>> values,
			List<String> totals, String CommunityName, String StatisticsTime,
			String Statistics, String filename, OutputStream outputStream,
			int totalrepeatsize, int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("欠费统计表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("欠费统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 20.25);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName + "              统计时间："
				+ StatisticsTime + "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 18.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(58));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(70));
				break;
			case 2:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(155));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(91));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 15.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));
				try {
					float NumberCell = Float.parseFloat(celltemp);
					cellvalue.setCellValue(NumberCell);
				} catch (Exception e) {
					cellvalue.setCellValue(celltemp);

				}

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 22.5);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
				totalrepeatsize - 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("欠费合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			try {
				float NumberCell = Float.parseFloat(totals.get(k));
				cellvalue.setCellValue(NumberCell);
			} catch (Exception e) {

				cellvalue.setCellValue(totals.get(k));
			}

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 21.00);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
				totalrepeatsize - 1));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream("excel/" + filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutTuitionTablesBySingleCellAndMergeMonth() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("计费面积");
		titles.add("物业管理费");
		titles.add("公共维修金");
		titles.add("车位管理费");
		titles.add("车位维修金");
		titles.add("公摊水电费");
		titles.add("合计（元");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 11; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 6; k++) {

			if (k == 5) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outTuitionTablesBySingleCellAndMergeMonth(titles, values, totals,
				"旗山校区", "2016-05-01~2016-05-06", "饶辉",
				"欠费统计表（单小区明细-合并月份总计）.xls", null, 5, 11);

	}

	/**
	 * 功能：欠费统计表（单小区明细-拆分月份显示）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * @param monthtitles
	 *            (月份标题的集合)
	 * @param secondtitles
	 *            (二级标题的集合)
	 * @param bigtotals
	 *            (大合计的集合)
	 * @param totalTotal
	 *            (合计的合计的集合)
	 * 
	 * */
	public static void outTuitionTablesBySingleCellAndSplitMonth(
			List<String> titles, List<String> monthtitles,
			List<String> secondtitles, List<List<String>> values,
			List<String> totals, List<String> bigtotals, String totalTotal,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		SXSSFWorkbook  workbook = new SXSSFWorkbook();
		Sheet sheet = workbook.createSheet("欠费统计表.xlsx");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		Font font1 = workbook.createFont();
		CellStyle style1 = workbook.createCellStyle();
		setXFontStyleAttribute(4, font1);

		cell.setCellStyle(SetXCellStyleAttribute(1, style1, font1));
		cell.setCellValue("欠费统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 20.25);
		Cell cell1 = Subtitle.createCell(0);
		CellStyle style11 = workbook.createCellStyle();
		Font font2 = workbook.createFont();
		setXFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetXCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName + "              统计时间："
				+ StatisticsTime + "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		Row rowT = sheet.createRow(3);
		row.setHeightInPoints((float) 18.75);
		rowT.setHeightInPoints((float) 18.75);
		CellStyle style2 = workbook.createCellStyle();
		Font font3 = workbook.createFont();

		setXFontStyleAttribute(6, font3);

		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			Cell celltempT = rowT.createCell(i);
			celltemp.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
			celltempT.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(58));
				sheet.addMergedRegion(new CellRangeAddress(2, 3, i, i));
				break;
			case 2:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(155));
				sheet.addMergedRegion(new CellRangeAddress(2, 3, i, i));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(70));
				sheet.addMergedRegion(new CellRangeAddress(2, 3, i, i));
				break;
			}

		}

		
		int Cellindex = 0;
		int ii = 0;
		for (Cellindex = titles.size(); Cellindex < monthtitles.size()
				* secondtitles.size() + titles.size(); Cellindex = Cellindex
				+ secondtitles.size()) {
			Cell celltemp = row.createCell(Cellindex);
			celltemp.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(monthtitles.get(ii));
			sheet.setColumnWidth(Cellindex, getPOIWidthByExcelPiex(82));
			ii++;
			for (int j = Cellindex + 1; j < Cellindex + secondtitles.size(); j++) {
				Cell celltempT = row.createCell(j);
				celltempT
						.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
				sheet.setColumnWidth(j, getPOIWidthByExcelPiex(82));

			}
			sheet.addMergedRegion(new CellRangeAddress(2, 2, Cellindex,
					Cellindex + secondtitles.size() - 1));
			int k = 0;
			for (int j = Cellindex; j < Cellindex + secondtitles.size(); j++) {
				Cell celltempT = rowT.createCell(j);
				celltempT
						.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
				celltempT.setCellValue(secondtitles.get(k));
				k++;

			}
		}
		
		
		Cell celltempF = row.createCell(Cellindex);
		celltempF.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
		sheet.setColumnWidth(Cellindex, getPOIWidthByExcelPiex(82));

		Cell celltempSF = rowT.createCell(Cellindex);
		celltempSF.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
		celltempSF.setCellValue("合计");
		int Rowindex = 4;
		System.out.println(new Date()+" 开始");
		
		/*List<Row>  bigRowlist = new ArrayList<Row>();
		for (int i = 0; i < values.size(); i++, Rowindex++) {
			Row rowvalues = sheet.createRow(Rowindex);
			rowvalues.setHeightInPoints((float) 15.00);
			bigRowlist.add(rowvalues);
			
		}
		
		List<Row> rowChildList1 = bigRowlist.subList(0, values.size()/2);
		List<Row> rowChildList2 = bigRowlist.subList(values.size()/2+1, values.size());
		
		List<List<String>> valueChlidList1 = values.subList(0, values.size()/2);
		List<List<String>> valueChlidList2 = values.subList(values.size()/2+1, values.size());
		
		MultithreadForRow multithreadForRow1 = new MultithreadForRow(valueChlidList1, rowChildList1, style2, font3);
		MultithreadForRow multithreadForRow2 = new MultithreadForRow(valueChlidList2, rowChildList2, style2, font3);
		
		new Thread(multithreadForRow1).run();
		new Thread(multithreadForRow2).run();*/
		
		
		for (int i = 0; i < values.size(); i++, Rowindex++) {
			Row rowvalues = sheet.createRow(Rowindex);
			rowvalues.setHeightInPoints((float) 15.00);
			List<String> excelCells = values.get(i);

			
			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue
						.setCellStyle(SetXCellStyleAttribute(2, style2, font3));

				cellvalue.setCellValue(celltemp);

			}
		}

		System.out.println(new Date()+" 结束");
		Row rowtotal = sheet.createRow(Rowindex);
		rowtotal.setHeightInPoints((float) 15.00);
		Row rowtotalS = sheet.createRow(Rowindex + 1);
		rowtotalS.setHeightInPoints((float) 15.00);

		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetXCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("欠费合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetXCellStyleAttribute(2, style2, font3));

		}
		
		for (int l = 0; l < totalrepeatsize; l++) {
			Cell cell2totals = rowtotalS.createCell(l);
			cell2totals.setCellStyle(SetXCellStyleAttribute(2, style2, font3));

		}
		
		sheet.addMergedRegion(new CellRangeAddress(Rowindex, Rowindex + 1, 0,
				totalrepeatsize - 1));
		
		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetXCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}
		

		int T = 0;
		for (int k = 0; k < bigtotals.size() * secondtitles.size(); k = k
				+ secondtitles.size()) {
			Cell cellvalue = rowtotalS.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetXCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(bigtotals.get(T++));

			for (int j = k + totalrepeatsize + 1; j < k + secondtitles.size()
					+ totalrepeatsize; j++) {

				Cell cellvalueT = rowtotalS.createCell(j);
				cellvalueT
						.setCellStyle(SetXCellStyleAttribute(2, style2, font3));

			}

			sheet.addMergedRegion(new CellRangeAddress(Rowindex + 1,
					Rowindex + 1, k + totalrepeatsize, k + totalrepeatsize
							+ secondtitles.size() - 1));

		}
		

		Cell cellTotal = rowtotalS.createCell(totalrepeatsize
				+ bigtotals.size() * secondtitles.size());
		cellTotal.setCellStyle(SetXCellStyleAttribute(2, style2, font3));

		cellTotal.setCellValue(totalTotal);

		Row rowtime = sheet.createRow(Rowindex + 2);
		rowtime.setHeightInPoints((float) 21.00);

		Cell Celltime = rowtime.createCell(0);
		CellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetXCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(Rowindex + 2, Rowindex + 2,
				0, totalrepeatsize - 1));

		
		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		

	}

	@Test
	public void testoutTuitionTablesBySingleCellAndSplitMonth() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("计费面积");

		List<String> secondtitles = new ArrayList<String>();
		secondtitles.add("物业管理费");
		secondtitles.add("公共维修金");
		secondtitles.add("车位管理费");
		secondtitles.add("车位维修金");
		secondtitles.add("公摊水电费");

		List<String> monthtitles = new ArrayList<String>();

		monthtitles.add("2016.01");
		monthtitles.add("2016.02");
		monthtitles.add("2016.03");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 10; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 21; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 16; k++) {

			if ((k + 1) % 5 == 0) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		List<String> bigtotals = new ArrayList<String>();
		bigtotals.add("10000");
		bigtotals.add("10000");
		bigtotals.add("10000");

		outTuitionTablesBySingleCellAndSplitMonth(titles, monthtitles,
				secondtitles, values, totals, bigtotals, "30000", "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "excel/欠费统计表（单小区明细-拆分月份显示）.xls",
				null, 5, 21);

	}

	/**
	 * 功能：欠费统计表（多个小区总计）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * 
	 * */
	public static void outTuitionTablesByCells(List<String> titles,
			List<List<String>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("欠费统计表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("欠费统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 20.25);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("      统计时间：" + StatisticsTime
				+ "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 18.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(58));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(88));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(90));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 15.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

				cellvalue.setCellValue(celltemp);

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 22.5);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
				totalrepeatsize - 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("欠费合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 21.00);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
				totalrepeatsize));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutTuitionTablesByCells() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("小区名称");
		titles.add("物业管理费");
		titles.add("公共维修金");
		titles.add("车位管理费");
		titles.add("车位维修金");
		titles.add("公摊水电费");
		titles.add("合计（元");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 8; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 6; k++) {

			if (k == 5) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outTuitionTablesByCells(titles, values, totals, "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "欠费统计表（多个小区总计）.xls", null, 2, 8);

	}

	/**
	 * 功能：收费率统计表（单小区）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * 
	 * */
	public static void outRateTablesSingleCell(List<String> titles,
			List<List<String>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("收费率统计表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("收费率统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 22.25);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName + "              统计时间："
				+ StatisticsTime + "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 18.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(54));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(134));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(124));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 15.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

				cellvalue.setCellValue(celltemp);

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 22.5);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
				totalrepeatsize - 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 17.25);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
				totalrepeatsize - 1));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutRateTablesSingleCell() {

		List<String> titles = new ArrayList<String>();
		titles.add("序号");
		titles.add("收费类目");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 6; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 4; k++) {

			if (k == 5) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outRateTablesSingleCell(titles, values, totals, "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "收费率统计表（单小区）.xls", null, 2, 6);

	}

	/**
	 * 功能：收费率统计表（单小区-按类目划分）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * @param monthCount
	 *            (需要显示的月份的个数)
	 * 
	 * */
	public static void outRateTablesSingleCellByCategory(List<String> titles,
			Map<String, List<Map<String, Object>>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("收费率统计表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("收费率统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 22.25);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName + "              统计时间："
				+ StatisticsTime + "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 18.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(134));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(125));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(124));
				break;
			}

		}

		int index = 3;

		Set<String> keyset = values.keySet();
		for (String string : keyset) {
			List<Map<String, Object>> valuemap = values.get(string);
			int top = index;
			for (Map<String, Object> map : valuemap) {
				Row rowvalues = sheet.createRow(index);
				index++;
				rowvalues.setHeightInPoints((float) 15.00);
				BigDecimal payAmount = (BigDecimal) map.get("payAmount");
				BigDecimal oweAmount = (BigDecimal) map.get("oweAmount");
				BigDecimal totalAmount = (BigDecimal) map.get("totalAmount");
				BigDecimal rate = payAmount.divide(totalAmount, 2,
						BigDecimal.ROUND_HALF_UP);
				for (int i = 0; i < 6; i++) {

					Cell cellvalue = rowvalues.createCell(i);
					cellvalue.setCellStyle(SetCellStyleAttribute(2, style2,
							font3));

					switch (i) {
					case 0:
						cellvalue.setCellValue(map.get("name").toString());
						break;
					case 1:
						cellvalue.setCellValue(map.get("fee_date").toString());
						break;
					case 2:
						cellvalue.setCellValue(totalAmount.toString());
						break;
					case 3:
						cellvalue.setCellValue(payAmount.toString());
						break;
					case 4:
						cellvalue.setCellValue(oweAmount.toString());
						break;
					case 5:
						cellvalue.setCellValue("%"
								+ rate.multiply(BigDecimal.valueOf(100))
										.toString());
						break;

					default:
						break;
					}

				}
			}

			sheet.addMergedRegion(new CellRangeAddress(top, top
					+ valuemap.size() - 1, 0, 0));

		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 22.5);
		sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
				totalrepeatsize - 1));
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 17.25);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
				totalrepeatsize - 1));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutRateTablesSingleCellByCategory() {

		List<String> titles = new ArrayList<String>();
		titles.add("收费类目");
		titles.add("月份");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 12; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 6; j++) {
				if (j == 0) {
					temp.add("" + (i + 1) + "物业管理费");

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 4; k++) {

			if (k == 4) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}
		/*
		 * outRateTablesSingleCellByCategory(titles, values, totals, "旗山校区",
		 * "2016-05-01~2016-05-06", "饶辉", "收费率统计表（单小区-按类目划分）.xls", null, 2, 6,
		 * 3);
		 */

	}

	/**
	 * 功能：收费率统计表（多小区）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * 
	 * */
	public static void outRateTablesCells(List<String> titles,
			List<List<String>> values, List<String> totals,
			String CommunityName, String StatisticsTime, String Statistics,
			String filename, OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("收费率统计表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("收费率统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 22.25);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("              统计时间：" + StatisticsTime
				+ "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 18.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(134));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(126));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(115));
				break;
			}

		}

		int index = 3;
		for (int i = 0; i < values.size(); i++, index++) {
			Row rowvalues = sheet.createRow(index);
			rowvalues.setHeightInPoints((float) 15.00);
			List<String> excelCells = values.get(i);

			for (int j = 0; j < excelCells.size(); j++) {

				String celltemp = excelCells.get(j);
				Cell cellvalue = rowvalues.createCell(j);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));
				cellvalue.setCellValue(celltemp);

			}
		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 22.5);
		/*
		 * sheet.addMergedRegion(new CellRangeAddress(index, index, 0,
		 * totalrepeatsize - 1));
		 */
		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("合计（元）");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		for (int k = 0; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 17.25);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));
		/*
		 * sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0,
		 * totalrepeatsize - 1));
		 */

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutRateTablesCells() {

		List<String> titles = new ArrayList<String>();
		titles.add("小区名称");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 5; j++) {
				if (j == 0) {
					temp.add("" + (i + 1));

				} else if (j == 1) {
					temp.add("2016.0" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 4; k++) {

			if (k == 5) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		outRateTablesCells(titles, values, totals, "旗山校区",
				"2016-05-01~2016-05-06", "饶辉", "收费率统计表（多小区）.xls", null, 1, 5);

	}

	/**
	 * 功能：收费率统计表（单小区-按月份划分）
	 * 
	 * @param titles
	 *            (标题的集合)
	 * @param values
	 *            (数据行的集合)
	 * @param totals
	 *            (合计的集合)
	 * @param totalrate2
	 * @param CommunityName
	 *            (小区名)
	 * @param StatisticsTime
	 *            (统计时间)
	 * @param Statistics
	 *            (统计人)
	 * @param filename
	 *            (文件名 当outputStream为null 起作用)
	 * @param outputStream
	 *            (输出流)
	 * @param bigTitleRepeatSize
	 *            (最大标题占用的单元格的个数)
	 * @param totalrepeatsize
	 *            (合计这个项目占用的单元格的个数)
	 * @param categporyCount
	 *            (需要显示的收费项目的个数)
	 * 
	 * */
	public static void outRateTablesSingleCellByMonth(List<String> titles,
			Map<String, List<Map<String, Object>>> values, List<String> totals,
			Map<String, BigDecimal> totalrate, String CommunityName,
			String StatisticsTime, String Statistics, String filename,
			OutputStream outputStream, int totalrepeatsize,
			int bigTitleRepeatSize) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("收费率统计表.xls");

		// 第一行
		Row rowBigtitle = sheet.createRow(0);
		rowBigtitle.setHeightInPoints((float) 22.5);

		Cell cell = rowBigtitle.createCell(0);
		HSSFFont font1 = workbook.createFont();
		HSSFCellStyle style1 = workbook.createCellStyle();
		setFontStyleAttribute(4, font1);

		cell.setCellStyle(SetCellStyleAttribute(1, style1, font1));
		cell.setCellValue("收费率统计表");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				bigTitleRepeatSize - 1));

		// 第二行
		Row Subtitle = sheet.createRow(1);
		Subtitle.setHeightInPoints((float) 22.25);
		Cell cell1 = Subtitle.createCell(0);
		HSSFCellStyle style11 = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		setFontStyleAttribute(5, font2);
		cell1.setCellStyle(SetCellStyleAttribute(1, style11, font2));
		cell1.setCellValue("小区名称：" + CommunityName + "              统计时间："
				+ StatisticsTime + "              统计人：" + Statistics);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
				bigTitleRepeatSize - 1));

		Row row = sheet.createRow(2);
		row.setHeightInPoints((float) 18.75);
		HSSFCellStyle style2 = workbook.createCellStyle();
		HSSFFont font3 = workbook.createFont();

		setFontStyleAttribute(6, font3);
		for (int i = 0; i < titles.size(); i++) {
			Cell celltemp = row.createCell(i);
			celltemp.setCellStyle(SetCellStyleAttribute(2, style2, font3));
			celltemp.setCellValue(titles.get(i));

			switch (i) {
			case 0:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(79));
				break;

			case 1:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(134));
				break;

			default:
				sheet.setColumnWidth(i, getPOIWidthByExcelPiex(110));
				break;
			}

		}

		int index = 3;
		Set<String> keyset = values.keySet();
		TreeSet<String> treeset = new TreeSet<>(keyset);

		for (String string : treeset) {
			List<Map<String, Object>> valuemap = values.get(string);
			int top = index;
			for (Map<String, Object> map : valuemap) {
				Row rowvalues = sheet.createRow(index);
				index++;
				rowvalues.setHeightInPoints((float) 15.00);
				BigDecimal payAmount = (BigDecimal) map.get("payAmount");
				BigDecimal oweAmount = (BigDecimal) map.get("oweAmount");
				BigDecimal totalAmount = (BigDecimal) map.get("totalAmount");
				BigDecimal rate = payAmount.divide(totalAmount, 2,
						BigDecimal.ROUND_HALF_UP);

				int i = 0;
				for (; i < 6; i++) {

					Cell cellvalue = rowvalues.createCell(i);
					cellvalue.setCellStyle(SetCellStyleAttribute(2, style2,
							font3));

					switch (i) {
					case 0:
						cellvalue.setCellValue(map.get("fee_date").toString());

						break;
					case 1:
						cellvalue.setCellValue(map.get("name").toString());
						break;
					case 2:
						cellvalue.setCellValue(totalAmount.toString());
						break;
					case 3:
						cellvalue.setCellValue(payAmount.toString());
						break;
					case 4:
						cellvalue.setCellValue(oweAmount.toString());
						break;
					case 5:
						cellvalue.setCellValue("%"
								+ rate.multiply(BigDecimal.valueOf(100))
										.toString());
						break;

					default:
						break;
					}

				}
				Cell cellvalue = rowvalues.createCell(i);
				cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));
				cellvalue.setCellValue(totalrate.get(map.get("fee_date"))
						.toString());

			}

			sheet.addMergedRegion(new CellRangeAddress(top, top
					+ valuemap.size() - 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(top, top
					+ valuemap.size() - 1, 6, 6));

		}

		Row rowtotal = sheet.createRow(index);
		rowtotal.setHeightInPoints((float) 22.5);

		Cell celltotal1 = rowtotal.createCell(0);
		celltotal1.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		celltotal1.setCellValue("合计");

		for (int l = 1; l < totalrepeatsize; l++) {
			Cell cell2total2 = rowtotal.createCell(l);
			cell2total2.setCellStyle(SetCellStyleAttribute(2, style2, font3));
		}

		int k = 0;
		for (; k < totals.size(); k++) {
			Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
			cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

			cellvalue.setCellValue(totals.get(k));

		}
		Cell cellvalue = rowtotal.createCell(k + totalrepeatsize);
		cellvalue.setCellStyle(SetCellStyleAttribute(2, style2, font3));

		Row rowtime = sheet.createRow(index + 1);
		rowtime.setHeightInPoints((float) 17.25);

		Cell Celltime = rowtime.createCell(0);
		HSSFCellStyle style3 = workbook.createCellStyle();
		Celltime.setCellStyle(SetCellStyleAttribute(3, style3, font2));
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		Celltime.setCellValue("制表时间:" + dateFormat.format(new Date()));

		OutputStream fileOut = null;

		try {
			if (outputStream != null) {

				fileOut = outputStream;

			} else {

				fileOut = new FileOutputStream(filename);
			}
			workbook.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	public void testoutRateTablesSingleCellByMonth() {

		List<String> titles = new ArrayList<String>();
		titles.add("月份");
		titles.add("收费类目");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");
		titles.add("合计（元）");
		titles.add("合计收费率");

		List<List<String>> values = new ArrayList<List<String>>();

		for (int i = 0; i < 100; i++) {
			List<String> temp = new ArrayList<String>();
			for (int j = 0; j < 8; j++) {
				if (j == 0) {
					temp.add("2016.0" + (i + 1));

				} else if (j == 1) {
					temp.add("物业管理费" + (i + 1));

				} else {
					temp.add("40");
				}
			}

			values.add(temp);
		}

		List<String> totals = new ArrayList<String>();

		for (int k = 0; k < 7; k++) {

			if (k == 6) {
				totals.add("2000");
			} else {
				totals.add("40");
			}

		}

		/*
		 * outRateTablesSingleCellByMonth(titles, values, totals, "旗山校区",
		 * "2016-05-01~2016-05-06", "饶辉", "收费率统计表（单小区-按月份划分）.xls", null, 1, 8,
		 * 5);
		 */

	}

}
